# -*- coding: utf-8 -*-
import os

import openpyxl
from django.http import HttpResponse
from openpyxl.styles import Alignment, Font, PatternFill
from openpyxl.utils import get_column_letter
from openpyxl.writer.excel import save_virtual_workbook

from demo_common_module.utils.exception_type import Exception400, Exception200


class ExcelUtils(object):

    @classmethod
    def check_decimal(cls, value, length=20, point=3):
        if cls.isfloat(value):
            value_float = float(value)
            if 0 <= value_float <= 10 ** length:  # 小数、整数
                cut_value = str(round(value_float, point + 1)).split(".")
                join_value = f"{cut_value[0]}.{cut_value[1][:point]}"
                out_value = float(join_value)
                return out_value
            else:
                return False
        else:
            return False

    @classmethod
    def makeup_quantity(cls, filled_data, n):
        """将需要补0的数据补满n位"""
        if cls.isint(filled_data):
            filled_data = str(int(filled_data))
        if len(filled_data) <= n:
            filled_data = '0' * (n - len(filled_data)) + filled_data  # 前面补0，补满n位
        return filled_data

    @staticmethod
    def check_reserved_length(all_field_dict, error_type,
                              reserved_fir, reserved_sec, reserved_thr, reserved_for, reserved_five):
        twenty = 20
        if len(reserved_fir) > twenty:
            error_type = f"{error_type}{all_field_dict['RESERVED_FIR']}长度超过{twenty}字符。"
        if len(reserved_sec) > twenty:
            error_type = f"{error_type}{all_field_dict['RESERVED_SEC']}长度超过{twenty}字符。"
        if len(reserved_thr) > twenty:
            error_type = f"{error_type}{all_field_dict['RESERVED_THR']}长度超过{twenty}字符。"
        if len(reserved_for) > twenty:
            error_type = f"{error_type}{all_field_dict['RESERVED_FOR']}长度超过{twenty}字符。"
        if len(reserved_five) > twenty:
            error_type = f"{error_type}{all_field_dict['RESERVED_FIVE']}长度超过{twenty}字符。"
        return error_type

    @staticmethod
    def check_file_type(file_obj):
        file_name = file_obj.name
        file_type = os.path.splitext(file_name)[1]
        if file_type != ".xlsx":
            raise Exception400("上传的文件不是Excel文件或格式不正确，请检查。")

    @staticmethod
    def none_to_str(col):
        if col is None:
            col = ""
        else:
            col = str(col)
        return col

    @staticmethod
    def none_to_zero(col):
        if col is None:
            col = 0
        return col

    @staticmethod
    def isint(value):
        try:
            int(value)
            return True
        except ValueError:
            return False

    @classmethod
    def isfloat(cls, value):
        try:
            float(value)
            return True
        except ValueError:
            return False

    @classmethod
    def check_percent(cls, value):
        """输入的值为小数、整数、百分比(字符串) > 校验通过转为float
        - 百分比(字符串)校验
        - 大小判断
        - 正确返回0 - 0.9999 - 1 ——hana表字段限制：decimal(5,4)
        """
        max_for_decimal = 6
        if cls.isfloat(value):
            value_float = float(value)
            if 0 <= value_float <= 1:  # 小数、整数
                cut_value = str(value_float)[:max_for_decimal]
                out_value = float(cut_value)
                return out_value
            else:
                return False
        else:
            if not value:
                return False
            elif value[-1] == "%":
                value = value.strip("%")
            else:
                return False
            if cls.isfloat(value):
                value_float = float(value)
                if 0 <= value_float <= 100:  # 百分比
                    cut_value = str(round(value_float / 100, max_for_decimal))[:max_for_decimal]
                    out_value = float(cut_value)
                    return out_value
                else:
                    return False
            else:
                return False

    @staticmethod
    def get_reserved_fields(row_values, all_field_dict):
        """获取预留字段的值"""
        reserved_fir = row_values.get(all_field_dict["RESERVED_FIR"], '')
        reserved_sec = row_values.get(all_field_dict["RESERVED_SEC"], '')
        reserved_thr = row_values.get(all_field_dict["RESERVED_THR"], '')
        reserved_for = row_values.get(all_field_dict["RESERVED_FOR"], '')
        reserved_five = row_values.get(all_field_dict["RESERVED_FIVE"], '')
        reserved_fir = ExcelUtils.none_to_str(reserved_fir)
        reserved_sec = ExcelUtils.none_to_str(reserved_sec)
        reserved_thr = ExcelUtils.none_to_str(reserved_thr)
        reserved_for = ExcelUtils.none_to_str(reserved_for)
        reserved_five = ExcelUtils.none_to_str(reserved_five)
        return reserved_fir, reserved_sec, reserved_thr, reserved_for, reserved_five

    @staticmethod
    def get_duplicates(upload_df, check_list):
        duplicated_list = upload_df[upload_df.duplicated(check_list)].index.values.tolist()
        return duplicated_list

    @staticmethod
    def get_no_duplicates(upload_df, key, check_list):
        no_duplicates_list = []
        grouped = upload_df.groupby(key)
        for name, group in grouped:
            no_duplicate_list = group.drop_duplicates(subset=check_list).index.values.tolist()
            if len(no_duplicate_list) > 1:
                no_duplicates_list = no_duplicates_list + no_duplicate_list
        return no_duplicates_list

    @staticmethod
    def check_headers(headers, column_cn_list):
        if headers != column_cn_list:
            raise Exception200("请下载最新模板进行数据导入。")

    @staticmethod
    def del_filed_by_table_name(table_name, results_list):
        if table_name == "tb_xxx":
            for field_dict in results_list:
                del field_dict["name"]
                del field_dict["age"]

    @staticmethod
    def load_excel(file_obj):
        wb = openpyxl.load_workbook(file_obj, data_only=True)
        sheets = wb.sheetnames
        worksheet = wb[sheets[0]]
        all_list, title_list = [], []

        for k, row in enumerate(worksheet.rows):
            # 获取表的字段
            if k == 0:
                title_list = [cell.value for cell in row]
            # 获取表的每行字段的值
            else:
                row_list = []
                for cell in row:
                    if isinstance(cell.value, bool):
                        value = str(cell.value).upper()
                    else:
                        value = cell.value
                    row_list.append(value)
                if any(row_list):
                    # 把字段名和字段值一一对应起来，生成字典并添加到row_list里
                    all_list.append(dict(zip(title_list, row_list)))
                else:
                    raise Exception200("表格中间含有空行，请更新后再上传。")
        return all_list

    @staticmethod
    def export_excel(sheet_name, results_list):
        wb = openpyxl.Workbook()
        w = wb.create_sheet(sheet_name, 0)
        i = 1
        for k, v in results_list[0].items():
            w.cell(row=1, column=i).value = k  # .replace("t_", "").replace("F_", "")
            s = chr(i + 64) + str(1)
            w[s].alignment = Alignment(horizontal='center', vertical='center')
            w[s].font = Font(bold=True)
            w.column_dimensions[chr(i + 64)].width = 20  # 列宽
            i = i + 1
        excel_row = 2
        # write data
        for i in range(0, len(results_list)):
            excel_col = 1
            for k, v in results_list[i].items():
                w.cell(row=excel_row, column=excel_col).value = v
                s = chr(excel_col + 64) + str(excel_row)
                w[s].alignment = Alignment(horizontal='center', vertical='center')
                excel_col = excel_col + 1
            excel_row = excel_row + 1
        return wb

    @staticmethod
    def open_excel_sheet():
        wb = openpyxl.Workbook()
        sheet = wb.get_sheet_by_name(wb.sheetnames[0])
        return wb, sheet

    @staticmethod
    def return_excel_response(wb, filename):
        response = HttpResponse(content=save_virtual_workbook(wb))
        response['Content-Type'] = "application/octet-stream;"
        response['Access-Control-Expose-Headers'] = "Content-Disposition"
        filename = filename.encode("utf-8").decode("ISO-8859-1")
        response['Content-Disposition'] = f"attachment; filename={filename}"
        return response

    @staticmethod
    def set_cell_fill(wb, cell):
        """为单元格设置填充色"""
        ws = wb.active
        # 单元格填充红色
        fill = PatternFill(fill_type="solid", start_color="FF0000", end_color="FF0000")
        if isinstance(cell, list):
            for each_cell in cell:
                ws[each_cell].fill = fill
        else:
            ws[cell].fill = fill

    @staticmethod
    def set_cell_width(wb, width):
        """为单元格设置列宽"""
        ws = wb.active
        for _ in range(1, ws.max_column + 1):
            letter = get_column_letter(_)
            ws.column_dimensions[letter].width = width
